use [Tfs_Warehouse]

--Requirements Tracability Matrix
DECLARE @ProjectGuid as varchar(36)
SET @ProjectGuid = (SELECT ProjectNodeGUID FROM GetProjectNodeInfoFromReportFolder('/TfsReports/DefaultCollection/RTM')) 
-- SELECT @ProjectGuid --> 8FDC8BBF-92C4-4151-8E51-8F313EBABC64

--SET @ProjectGuid = (SELECT ProjectNodeGUID FROM GetProjectNodeInfoFromReportFolder('/TfsReports/DefaultCollection/GDT2')) 
-- SELECT @ProjectGuid -->  87CC4E20-C975-4EB3-AA58-3EC5767F9D6E
	
DECLARE @AreaParam as varchar(256)
SET @AreaParam = '*'

DECLARE @IterationParam as varchar(256)
SET @IterationParam = '*'
--SET @IterationParam = (SELECT IterationSK FROM DimIteration WHERE ProjectGUID = @ProjectGuid AND IterationPath = '\RTM\Iteration 1')
--SET @IterationParam = (SELECT IterationSK FROM DimIteration WHERE ProjectGUID = @ProjectGuid AND IterationPath = '\RTM\Iteration 2')
--SET @IterationParam = (SELECT IterationSK FROM DimIteration WHERE ProjectGUID = @ProjectGuid AND IterationPath = '\RTM\Iteration 3')

DECLARE @AllNoFilter as varchar(20)
SET @AllNoFilter = 'All (No Filter)' 

DECLARE @DeliverableCategory as varchar(256)
SET @DeliverableCategory = 'Requirement'

DECLARE @ClosedStateCategory as varchar(256)
SET @ClosedStateCategory = 'Closed, Complete, Cut'

DECLARE @Active as varchar(128)
SET @Active = 'Active'

DECLARE @Closed as varchar(128)
SET @Closed = 'Closed'

DECLARE @Bug as varchar(128)
SET @Bug = 'Bug'

DECLARE @Proposed as varchar(128)
SET @Proposed = 'Proposed'

DECLARE @Task as varchar(128)
SET @Task = 'Task'

DECLARE @TestCase as varchar(128)
SET @TestCase = 'Test Case'

DECLARE @Resolved as varchar(128)
SET @Resolved = 'Resolved'

DECLARE @OwnValues as varchar(128)
SET @OwnValues = 'Own Values'

DECLARE @UseCase as varchar(128)
SET @UseCase = 'Use Case'

DECLARE @ChangeRequest as varchar(128)
SET @ChangeRequest = 'Change Request'

DECLARE @SupportTicket as varchar(128)
SET @SupportTicket = 'Support Ticket'

DECLARE @UserStory as varchar(128)
SET @UserStory = 'User Story'

DECLARE @Requirement as varchar(128)
SET @Requirement = 'Requirement'

DECLARE @IncludeTasks INT
SET @IncludeTasks = 1

DECLARE @DisplayOwnValues INT
SET @DisplayOwnValues = 0

DECLARE @SelectedAreaList TABLE (AreaSK int, [AreaPath] nvarchar(4000) null);
INSERT @SelectedAreaList 
	SELECT AreaSK, [AreaPath]
	  FROM DimArea
	 WHERE ProjectGUID = @ProjectGuid
	   AND '*' In (@AreaParam) OR CONVERT(NVARCHAR, AreaSK) In (@AreaParam)
;

DECLARE @ChildAreas TABLE (AreaSK int, [AreaPath] nvarchar(4000) null);
INSERT @ChildAreas 
	SELECT DISTINCT a.AreaSK, a.[AreaPath]
	  FROM DimArea a
	 INNER JOIN @SelectedAreaList sa ON a.[AreaPath] LIKE sa.[AreaPath] + '\%';
;

DECLARE @AreasAndChildren TABLE (AreaSK int, [AreaPath]  nvarchar(4000) null);
INSERT @AreasAndChildren 

	SELECT AreaSK, [AreaPath] FROM @SelectedAreaList
	UNION
	SELECT AreaSK, [AreaPath] FROM @ChildAreas
;

DECLARE @SelectedIterationList TABLE (IterationSk int, [IterationPath] nvarchar(4000) null);
INSERT @SelectedIterationList
	SELECT IterationSK, [IterationPath] 
	FROM DimIteration
	WHERE ProjectGUID = @ProjectGuid
	  AND '*' IN (@IterationParam) OR CONVERT(NVARCHAR, IterationSk) IN (@IterationParam)
;

DECLARE @ChildIterations TABLE (IterationSk int, [IterationPath] nvarchar(4000) null);
INSERT @ChildIterations
	SELECT DISTINCT i.IterationSK, i.[IterationPath]
	  FROM DimIteration i
	INNER JOIN @SelectedIterationList si ON i.[IterationPath] LIKE si.[IterationPath] + '\%'
;

DECLARE @IterationsAndChildren TABLE (__ID int, [Iteration Path] nvarchar(4000) null);
INSERT @IterationsAndChildren

	SELECT IterationSk, [IterationPath] FROM @SelectedIterationList
	UNION
	SELECT IterationSk, [IterationPath] FROM @ChildIterations
;

-- TeamProjectCollection - Needed because links are done at the TPC level
DECLARE @TeamProjectCollectionSK int; 
SET @TeamProjectCollectionSK = (SELECT ParentNodeSK FROM DimTeamProject WHERE ProjectNodeGUID = @ProjectGuid );

-- Link Types
DECLARE @ParentWorkItemLinkTypeSK int; 
SET @ParentWorkItemLinkTypeSK = (SELECT [WorkItemLinkTypeSK] FROM [DimWorkItemLinkType] WHERE [LinkID] < 0 AND [ReferenceName] = 'System.LinkTypes.Hierarchy' AND TeamProjectCollectionSK = @TeamProjectCollectionSK);
DECLARE @ChildWorkItemLinkTypeSK int; 
SET @ChildWorkItemLinkTypeSK = (SELECT [WorkItemLinkTypeSK] FROM [DimWorkItemLinkType] WHERE [LinkID] > 0 AND [ReferenceName] = 'System.LinkTypes.Hierarchy' AND TeamProjectCollectionSK = @TeamProjectCollectionSK);
DECLARE @TestedByLinkTypeSK int;
SET @TestedByLinkTypeSK = (SELECT [WorkItemLinkTypeSK] FROM [DimWorkItemLinkType] WHERE [LinkID] > 0 AND [ReferenceName] = 'Microsoft.VSTS.Common.TestedBy' AND TeamProjectCollectionSK = @TeamProjectCollectionSK);
DECLARE @TestsLinkTypeSK int;
SET @TestsLinkTypeSK = (SELECT [WorkItemLinkTypeSK] FROM [DimWorkItemLinkType] WHERE [LinkID] < 0 AND [ReferenceName] = 'Microsoft.VSTS.Common.TestedBy' AND TeamProjectCollectionSK = @TeamProjectCollectionSK);
-- General strategy for rollup:
--    Asssign a level to each node;
--    For level = max level to 0 do { sum nodes at this level into their parents }
--
-- Assign a hierarchy level to each node. Start at the roots and work down.
--
-- This is selecting all root deliverable workitems that have no parents
DECLARE @RootNodeIDs TABLE (ID int, WorkItemType nvarchar(256));
INSERT @RootNodeIDs
	-- This is selecting all deliverable workitems
	SELECT cwi.[System_Id] AS ID, cwi.[System_WorkItemType] as WorkItemType
	FROM [CurrentWorkItemView] cwi
	WHERE cwi.[System_WorkItemType] IN (@DeliverableCategory)
		AND cwi.[ProjectNodeGUID] = @ProjectGuid
EXCEPT
	-- This is selecting all workitems that have a parent and have not been deleted
	SELECT lh.SourceWorkItemID AS ID, NULL as WorkItemType 
	FROM FactWorkItemLinkHistory lh
	WHERE lh.WorkItemLinkTypeSK = @ParentWorkItemLinkTypeSK
		AND lh.RemovedDate = CONVERT(DATETIME, '9999', 126)
		AND lh.TeamProjectCollectionSK = @TeamProjectCollectionSK
;
--SELECT * FROM @RootNodeIDs

DECLARE @Hierarchy Table(ID int, ParentID int, [Level] int, [Path] nvarchar(4000), WorkItemType nvarchar(256));
WITH
Hierarchy (ID, ParentID, [Level], [Path], WorkItemType) AS
(
	SELECT	ID, 
			NULL as ParentID, 
			0 as [Level], 
			CAST('/' + STR(ID) AS nvarchar(256)) as [Path], 
			WorkItemType
	FROM @RootNodeIDs rootId 

UNION ALL

	SELECT	flh.TargetWorkItemID as ID, 
			parent.ID as ParentID, 
			parent.Level + 1 as [Level], 
			CAST(parent.Path + '/' + STR(flh.TargetWorkItemID) AS nvarchar(256)) as [Path], 
			wi.[System_WorkItemType] AS WorkItemType
	FROM Hierarchy parent 
		JOIN FactWorkItemLinkHistory flh 
			ON flh.WorkItemLinkTypeSK = @ChildWorkItemLinkTypeSK
				AND flh.SourceWorkItemID = parent.ID
				AND flh.RemovedDate = CONVERT(DATETIME, '9999', 126)
				AND flh.TeamProjectCollectionSK = @TeamProjectCollectionSK
		JOIN [CurrentWorkItemView] wi ON flh.TargetWorkItemID = wi.[System_ID]  	
			--AND (wi.[System_WorkItemType] &lt;&gt; @Bug)
			AND (wi.System_State != @Proposed OR wi.System_WorkItemType != @Task)	-- Exclude proposed tasks from the rollup
			AND wi.ProjectNodeGUID  = @ProjectGuid		
	WHERE parent.Path NOT LIKE CAST('%' + STR(flh.TargetWorkItemID) + '%' AS nvarchar(20)) AND parent.Level < 20 
)

-- Add the selected Work Items to the Hierarchy
INSERT @Hierarchy SELECT * FROM Hierarchy;

-- Now add the Test Cases to the Hierarchy
INSERT @Hierarchy	
	SELECT	flh.TargetWorkItemID as ID, 
			h.ID as ParentID, 
			h.Level + 1 as [Level], 
			CAST(h.Path + '/' + STR(flh.TargetWorkItemID) AS nvarchar(256)) as [Path], 
			wi.[System_WorkItemType] AS WorkItemType
			FROM @Hierarchy h
		JOIN FactWorkItemLinkHistory flh 
			ON flh.SourceWorkItemID = h.ID
				AND flh.WorkItemLinkTypeSK = @TestedByLinkTypeSK
				AND flh.RemovedDate = CONVERT(DATETIME, '9999', 126) 
				AND flh.TeamProjectCollectionSK = @TeamProjectCollectionSK
		JOIN [CurrentWorkItemView] wi ON flh.TargetWorkItemID = wi.[System_ID]  	
			AND wi.[System_WorkItemType] = @TestCase
			AND wi.ProjectNodeGUID  = @ProjectGuid
	WHERE h.Path NOT LIKE CAST('%' + STR(flh.TargetWorkItemID) + '%' AS nvarchar(20)) AND h.Level < 20 
;
--SELECT * FROM @Hierarchy
-- Hierarchy now contains all the Tasks, Test Cases and their ancestors that we're interested in.		
-- Get the list deliverable workitems that have Test Cases linked
DECLARE @TestCases Table (DeliverableID int, TestCaseID int, WorkItemType nvarchar(256));
INSERT @TestCases
	SELECT	h.ID, 
			flh.TargetWorkItemID,
			wi.System_WorkItemType
	FROM @Hierarchy h
		JOIN FactWorkItemLinkHistory flh 
			ON flh.SourceWorkItemID = h.ID
				AND flh.WorkItemLinkTypeSK = @TestedByLinkTypeSK
				AND flh.RemovedDate = CONVERT(DATETIME, '9999', 126) 
				AND flh.TeamProjectCollectionSK = @TeamProjectCollectionSK
		JOIN [CurrentWorkItemView] wi ON flh.TargetWorkItemID = wi.[System_ID]  	
			AND wi.[System_WorkItemType] = @TestCase
			AND wi.ProjectNodeGUID  = @ProjectGuid
;
--SELECT * FROM @TestCases;
-- Finally add the BUGS that are linked to Test Cases into the Hierarchy
INSERT @Hierarchy	
	SELECT	flh.TargetWorkItemID as ID, 
			h.ID as ParentID, 
			h.Level + 1 as [Level], 
			CAST(h.Path + '/' + STR(flh.TargetWorkItemID) AS nvarchar(256)) as [Path], 
			wi.[System_WorkItemType] AS WorkItemType
			FROM @Hierarchy h
		JOIN @TestCases tc ON tc.TestCaseID = h.ID
		JOIN FactWorkItemLinkHistory flh ON flh.SourceWorkItemID = h.ID
				AND flh.WorkItemLinkTypeSK = @TestsLinkTypeSK
				AND flh.RemovedDate = CONVERT(DATETIME, '9999', 126) 
				AND flh.TeamProjectCollectionSK = @TeamProjectCollectionSK
		JOIN [CurrentWorkItemView] wi ON flh.TargetWorkItemID = wi.[System_ID]  	
			AND wi.[System_WorkItemType] = @Bug
			AND wi.ProjectNodeGUID  = @ProjectGuid
	WHERE h.Path NOT LIKE CAST('%' + STR(flh.TargetWorkItemID) + '%' AS nvarchar(20)) AND h.Level < 20 
;	
-- Hierarchy now contains all the Tasks, Test Cases, Bugs and their ancestors that we're interested in.
--SELECT * FROM @Hierarchy

-- Get Test Results associated with Test Cases linked to deliverable workitems
DECLARE @TestResults TABLE (ID int, Passed int, Failed int, NotRun int, TestCaseCount int, TestCaseIdList nvarchar(2000))
INSERT @TestResults 
	SELECT ID, Passed, Failed, NotRun, TestCaseCount, NULL
	FROM
	(SELECT tc.DeliverableID AS ID, 
		SUM(CASE WHEN testResult.ResultOutcomeId = 2 THEN 1 ELSE 0 END) Passed, 
		SUM(CASE WHEN testResult.ResultOutcomeId = 3 THEN 1 ELSE 0 END) Failed, 
		SUM(CASE WHEN testResult.ResultOutcomeId = 255 THEN 1 ELSE 0 END) NotRun,
		COUNT(tc.TestCaseID) TestCaseCount
	FROM @Hierarchy h 
		JOIN @TestCases tc on h.ID = tc.DeliverableID
		LEFT OUTER JOIN (
			SELECT f.TestCaseId, r.OutcomeId AS ResultOutcomeId,
				ROW_NUMBER() OVER (PARTITION BY f.TestCaseId, f.PointId ORDER BY f.ChangeNumber DESC) rn
			FROM FactTestResult f
				INNER JOIN DimTestResult r ON r.ResultSK = f.ResultSK
                INNER JOIN DimTeamProject tp ON f.TeamProjectSK = tp.ProjectNodeSK AND tp.ProjectNodeGUID = @ProjectGuid 
			WHERE f.PointRecordCount = 1
		) testResult ON testResult.TestCaseId = tc.TestCaseID AND testResult.rn=1
	GROUP BY  tc.DeliverableID
	) Counts
	INNER JOIN [CurrentWorkItemView] wi ON ID = wi.[System_Id]
		AND wi.ProjectNodeGUID = @ProjectGuid;
;
--SELECT * FROM @TestResults;
-- Hack to make sure that TestCase that have been defined as workitems and not linked to test plans are captured 
UPDATE @TestResults SET NotRun = TestCaseCount
WHERE Passed = 0 and Failed = 0 and NotRun = 0

--SELECT * FROM @TestResults;
-- Get the list deliverable workitems that have Bugs linked
DECLARE @Bugs Table (ParentID int, BugID int, WorkItemType nvarchar(256));
INSERT @Bugs
SELECT	h.ParentID, 
			flh.TargetWorkItemID,
			wi.System_WorkItemType
	FROM @Hierarchy h
		JOIN FactWorkItemLinkHistory flh 
			ON flh.TargetWorkItemID = h.ID
				AND (flh.WorkItemLinkTypeSK = @TestsLinkTypeSK OR flh.WorkItemLinkTypeSK = @ChildWorkItemLinkTypeSK)
				AND flh.RemovedDate = CONVERT(DATETIME, '9999', 126) 
				AND flh.TeamProjectCollectionSK = @TeamProjectCollectionSK
		JOIN [CurrentWorkItemView] wi ON flh.TargetWorkItemID = wi.[System_ID]  	
			AND wi.[System_WorkItemType] = @Bug
			AND wi.ProjectNodeGUID  = @ProjectGuid
;
--SELECT * FROM @Bugs;
 --Get the Bug Counts linked to the deliverable workitems
DECLARE @BugCounts Table (ParentID int, ActiveBugs int, ResolvedBugs int, ClosedBugs int, ProposedBugs int, BugCount int, BugsIdList nvarchar(2000))
INSERT @BugCounts
	SELECT h.ID, 
		SUM (CASE WHEN wi.[System_State] = @Active THEN 1 ELSE 0 END) AS Active, 
		SUM (CASE WHEN wi.[System_State] = @Resolved THEN 1 ELSE 0 END) AS Resolved,
		SUM (CASE WHEN wi.[System_State] = @Closed THEN 1 ELSE 0 END) AS Closed,
		SUM (CASE WHEN wi.[System_State] = @Proposed THEN 1 ELSE 0 END) AS Proposed,		
		SUM (CASE WHEN wi.[System_State] = @Active THEN 1 ELSE 0 END) +
		SUM (CASE WHEN wi.[System_State] = @Resolved THEN 1 ELSE 0 END) +
		SUM (CASE WHEN wi.[System_State] = @Closed THEN 1 ELSE 0 END) +
		SUM (CASE WHEN wi.[System_State] = @Proposed THEN 1 ELSE 0 END) AS BugCount,
		NULL AS BugsIdList
	FROM @Hierarchy h
		JOIN FactWorkItemLinkHistory flh 
			ON flh.SourceWorkItemID = h.ID
			AND flh.TeamProjectCollectionSK = @TeamProjectCollectionSK
		JOIN [CurrentWorkItemView] wi 
			ON wi.[System_WorkItemType] = @Bug 
			AND wi.[System_Id] = flh.TargetWorkItemID
			AND flh.RemovedDate = CONVERT(DATETIME, '9999', 126)
			AND wi.[ProjectNodeGUID] = @ProjectGuid
	GROUP BY h.ID
;	
--SELECT * FROM @BugCounts; 
-- Add the Bugs linked to the Test Cases which are linked to the deliverable workitems
-- Walks the links from the user stories to test cases (via the tested by link), and then to
-- bugs that are linked to the test case. We don't need to join to the test case in the work
-- item history view.
--
--	[WIT:User Story/Requirement] --&gt; [Link:Tested By]--&gt; [Link:any type] --&gt; [WIT:Bug]
--INSERT @Bugs
--SELECT tc.DeliverableID, 
--	SUM (CASE WHEN wi.[System_State] = @Active THEN 1 ELSE 0 END) Active, 
--	SUM (CASE WHEN wi.[System_State] = @Resolved THEN 1 ELSE 0 END) Resolved,
--	SUM (CASE WHEN wi.[System_State] = @Closed THEN 1 ELSE 0 END) Closed,
--	SUM (CASE WHEN wi.[System_State] = @Proposed THEN 1 ELSE 0 END) Proposed
--FROM @TestCases tc
--	JOIN FactWorkItemLinkHistory flh 
--		ON flh.SourceWorkItemID = tc.TestCaseID
--		AND flh.RemovedDate = CONVERT(DATETIME, '9999', 126)
--		AND flh.TeamProjectCollectionSK = @TeamProjectCollectionSK
--	JOIN [CurrentWorkItemView] wi
--		ON wi.[System_Id] = flh.TargetWorkItemID
--		AND wi.[System_WorkItemType] = @Bug 
--		AND wi.[ProjectNodeGUID] = @ProjectGuid
--GROUP BY tc.DeliverableID

--SELECT * FROM @Bugs
-- Get the ChangeSets linked to the deliverable workitems directly
DECLARE @ChangeSets Table (DeliverableID int, WorkItemType nvarchar(256), ChangeSetID int ); --, ChangesetTitle nvarchar(128) );
INSERT @ChangeSets
	SELECT h.id, h.WorkItemType, dc.ChangesetID 	
	FROM @Hierarchy h	
	JOIN dbo.FactWorkItemChangeset fwic
		ON fwic.WorkItemID = h.ID
		AND fwic.RemovedDateTime = CONVERT(DATETIME, '9999', 126)
		AND fwic.TeamProjectCollectionSK = @TeamProjectCollectionSK
	JOIN dbo.DimChangeset dc
		on dc.ChangesetSK = fwic.ChangesetSK
	WHERE WorkItemType = @Task or WorkItemType = @Bug
;
--SELECT * FROM @ChangeSets
DECLARE @ChangesetCounts Table (DeliverableID int, WorkItemType nvarchar(256), ChangesetCount int, ChangesetIdList nvarchar(2000) );
INSERT @ChangesetCounts
	SELECT h.id, h.WorkItemType,  
		COUNT(h.WorkItemType) AS ChangeSetCount,
		NULL AS ChangesetIdList	
	FROM @Hierarchy h	
	JOIN dbo.FactWorkItemChangeset fwic
		ON fwic.WorkItemID = h.ID
		AND fwic.RemovedDateTime = CONVERT(DATETIME, '9999', 126)
		AND fwic.TeamProjectCollectionSK = @TeamProjectCollectionSK
	JOIN dbo.DimChangeset dc
		on dc.ChangesetSK = fwic.ChangesetSK
	WHERE WorkItemType = @Task or WorkItemType = @Bug
	GROUP BY id, WorkItemType
	ORDER BY id
;
--SELECT * FROM @ChangesetCounts
--
-- Roll values up the hierarchy
--
DECLARE @Rollups TABLE 
	(ID int, ParentID int, [Level] int, [Path] nvarchar(256), WorkItemType nvarchar(256) null, Project nvarchar(256) null, [State] nvarchar(256) null,
	Remaining float, Completed float, RawRemaining float, RawCompleted float, StackRank float null,
	RawActiveBugs int, RawResolvedBugs int, RawClosedBugs int, RawProposedBugs int, RawTotalBugCount int,
	ActiveBugs int, ResolvedBugs int, ClosedBugs int, ProposedBugs int, TotalBugCount int, BugsIdList nvarchar(2000),
	RawPassed int, RawFailed int, RawNotRun int, RawTestCaseCount int,
	Passed int, Failed int, NotRun int, TestCaseCount int, TestCaseIdList nvarchar(2000),
	RawChangesetCount int, ChangesetCount int, ChangesetIdList nvarchar(2000),
	WIT nvarchar(256) null, Title nvarchar(256) null, 
	InScope int, ChildInScope int, -- is in the selected Area and Iteration; has a descendant in scope
	IsOpen int,  -- is not Closed
	AreaPath nvarchar(4000) null, IterationPath nvarchar(4000) null
	);

-- Initialize the table for the rolled up results.
INSERT @Rollups
	SELECT hierarchy.ID, hierarchy.ParentID, [Level], [Path], hierarchy.WorkItemType, [ProjectNodeName] as Project, [System_State] as [State],
		ISNULL(Microsoft_VSTS_Scheduling_RemainingWork, 0) Remaining, 
		ISNULL(Microsoft_VSTS_Scheduling_CompletedWork, 0) Completed,
		ISNULL(Microsoft_VSTS_Scheduling_RemainingWork, 0) RawRemaining, 
		ISNULL(Microsoft_VSTS_Scheduling_CompletedWork, 0) RawCompleted,
		ISNULL(Microsoft_VSTS_Common_StackRank, 1E+308) StackRank,
		ISNULL(bcs.ActiveBugs, 0) as RawActiveBugs, ISNULL(bcs.ResolvedBugs, 0) as RawResolvedBugs, ISNULL(bcs.ClosedBugs, 0) as RawClosedBugs, ISNULL(bcs.ProposedBugs, 0) as RawProposedBugs, ISNULL(bcs.BugCount, 0) as RawTotalBugCount,
		0 ActiveBugs, 0 ResolvedBugs, 0 ClosedBugs, 0 ProposedBugs, 0 TotalBugCount, NULL as BugsIdList,
		ISNULL(testResults.Passed, 0) as RawPassed, ISNULL(testResults.Failed, 0) as RawFailed, ISNULL(testResults.NotRun, 0) as RawNotRun, ISNULL(testResults.TestCaseCount, 0) as RawTestCaseCount,
		0 Passed, 0 Failed, 0 NotRun, 0 TestCaseCount,  NULL as TestCaseIdList, 
		ISNULL(csc.ChangesetCount, 0) as RawChangesetCount, 0 ChangesetCount, NULL as ChangesetIdList,
		[System_WorkItemType] WIT, [System_Title] Title,
		(CASE WHEN	[AreaPath] IN (SELECT [AreaPath] FROM @AreasAndChildren) 
			AND	[IterationPath] IN (SELECT [Iteration Path] FROM @IterationsAndChildren)
			THEN 1 ELSE 0
		END) InScope,
		0 ChildInScope,
		(CASE WHEN [System_State] NOT IN (@ClosedStateCategory) THEN 1 ELSE 0 END) IsOpen,
		[AreaPath] AreaPath,
		[IterationPath] IterationPath
	FROM @Hierarchy hierarchy
		INNER JOIN [CurrentWorkItemView] ON hierarchy.ID = [System_ID] AND ProjectNodeGUID = @ProjectGuid
		LEFT OUTER JOIN @TestResults testResults ON testResults.ID = hierarchy.ID
		LEFT OUTER JOIN @ChangesetCounts csc ON csc.DeliverableID = hierarchy.ID
		LEFT OUTER JOIN @BugCounts bcs ON bcs.ParentID = hierarchy.ID
;		

--SELECT * FROM @Rollups ORDER BY [Path];
-- Update the rollups table with the list of TestCase IDs scoped to the Iteration and Area; That is where InScope = 1
WITH
ScopedTestCases (DeliverableID, TestCaseID, WorkItemType) AS
(
	SELECT DeliverableID, TestCaseID, tc.WorkItemType
	FROM @TestCases tc
	INNER JOIN @Rollups r ON tc.TestCaseID = r.[ID] AND r.InScope = 1
)

UPDATE @Rollups 
	SET RawTestCaseCount = tcs.TestCaseCount, TestCaseIdList = tcs.TestCaseIdList
	FROM
		(SELECT DeliverableID, COUNT(WorkItemType) AS TestCaseCount,
		   TestCaseIdList = substring((SELECT ( ', ' + CAST(TestCaseID AS nvarchar(6)) )
								   FROM ScopedTestCases t2
								   WHERE t1.DeliverableID = t2.DeliverableID
								   ORDER BY DeliverableID, TestCaseID
								   FOR XML PATH( '' )
								  ), 3, 1000 )
								  FROM ScopedTestCases t1 
		 GROUP BY DeliverableID, WorkItemType
		) AS tcs
WHERE [ID] = tcs.DeliverableID
;
	
--SELECT * FROM @Rollups ORDER BY [Path];
-- Update the rollups table with the list of Bug IDs scoped to the Iteration and Area; That is where InScope = 1
WITH
ScopedBugs (ParentID, BugID, WorkItemType) AS
(
	SELECT bugs.ParentID, BugID, bugs.WorkItemType
	FROM @Bugs bugs
	INNER JOIN @Rollups r ON bugs.ParentID = r.[ID] AND r.InScope = 1
)

UPDATE @Rollups 
SET RawTotalBugCount = bugs.TotalBugCount, BugsIdList = bugs.BugsIdList 
	FROM
		(SELECT
			   t1.ParentID,
			   t1.WorkItemType,
			   COUNT(t1.WorkItemType) AS TotalBugCount,
			   BugsIdList = substring((SELECT ( ', ' + CAST(BugID AS nvarchar(6)) )
									   FROM ScopedBugs t2
									   WHERE t1.ParentID = t2.ParentID
									   ORDER BY 
										  ParentID, BugID
									   FOR XML PATH( '' )
									  ), 3, 1000 )
									  FROM ScopedBugs t1 
			GROUP BY ParentID, WorkItemType
		) AS bugs
	WHERE  ID = bugs.ParentID
; 

--SELECT * FROM @Rollups ORDER BY [Path];
-- Update the rollups table with the list of Changeset IDs scoped to the Iteration and Area; That is where InScope = 1
WITH
ScopedChangesets (ParentID, ChangesetID, WorkItemType) AS
(
	SELECT DeliverableID, ChangesetID, cs.WorkItemType
	FROM @ChangeSets cs
	INNER JOIN @Rollups r ON cs.DeliverableID = r.[ID] AND r.InScope = 1
)

UPDATE @Rollups 
SET RawChangesetCount = changesets.ChangesetCount, ChangesetIdList = changesets.ChangesetIdList 
	FROM
		(SELECT
			   t1.ParentID,
			   t1.WorkItemType,
			   COUNT(t1.WorkItemType) AS ChangesetCount,
			   ChangesetIdList = substring((SELECT ( ', ' + CAST(ChangesetID AS nvarchar(6)) )
									   FROM ScopedChangesets t2
									   WHERE t1.ParentID = t2.ParentID
									   ORDER BY 
										  ParentID, ChangesetID
									   FOR XML PATH( '' )
									  ), 3, 1000 )
									  FROM ScopedChangesets t1 
			GROUP BY ParentID, WorkItemType
		) AS changesets
	WHERE  ID = changesets.ParentID
; 
--SELECT * FROM @Rollups ORDER BY [Path];
--
-- Sum up the hierarchy
-- Needs to be a loop because recursive CTEs don't admit GROUP BY
--
-- Accumulate the sums in a separate table - repeated UPDATEs are expensive.
-- Also rollup whether a parent has a child that is in scope.
--
DECLARE @Sums TABLE (ID int, Remaining float null, Completed float null,
		ActiveBugs int, ResolvedBugs int, ClosedBugs int, ProposedBugs int, TotalBugCount int,
		Passed int, Failed int, NotRun int, TestCaseCount int, ChangesetCount int,
		ChildInScope int);
DECLARE @Level int;
SET @Level = (SELECT Max(Level) FROM @Rollups);
WHILE @Level > 0

BEGIN
	WITH IncludeRaw AS
	(	SELECT 
			ru.ID AS ID, ru.ParentID as ParentID,
			ISNULL(s.Remaining, 0) + CASE WHEN ru.InScope > 0 THEN ru.Remaining ELSE 0 END AS Remaining, -- own totals are included in the rollup
			ISNULL(s.Completed, 0) + CASE WHEN ru.InScope > 0 THEN ru.Completed ELSE 0 END AS Completed, -- own totals are included in the rollup
			--ISNULL(s.Remaining, CASE WHEN ru.InScope > 0 THEN ru.Remaining ELSE 0 END) AS Remaining, -- own totals not included in rollup
			--ISNULL(s.Completed, CASE WHEN ru.InScope > 0 THEN ru.Completed ELSE 0 END) AS Completed, -- own totals not included in rollup
			ISNULL(s.ActiveBugs, 0) + CASE WHEN ru.InScope > 0 THEN ru.RawActiveBugs ELSE 0 END AS ActiveBugs,
			ISNULL(s.ResolvedBugs, 0) + CASE WHEN ru.InScope > 0 THEN ru.RawResolvedBugs ELSE 0 END AS ResolvedBugs,
			ISNULL(s.ClosedBugs, 0) + CASE WHEN ru.InScope > 0 THEN ru.RawClosedBugs ELSE 0 END AS ClosedBugs,
			ISNULL(s.ProposedBugs, 0) + CASE WHEN ru.InScope > 0 THEN ru.RawProposedBugs ELSE 0 END AS ProposedBugs,		
			ISNULL(s.TotalBugCount, 0) + CASE WHEN ru.InScope > 0 THEN ru.RawTotalBugCount ELSE 0 END AS TotalBugCount,
			ISNULL(s.Passed, 0) + CASE WHEN ru.InScope > 0 THEN ru.RawPassed ELSE 0 END AS Passed,
			ISNULL(s.Failed, 0) + CASE WHEN ru.InScope > 0 THEN ru.RawFailed ELSE 0 END AS Failed,
			ISNULL(s.NotRun, 0) + CASE WHEN ru.InScope > 0 THEN ru.RawNotRun ELSE 0 END AS NotRun,
			ISNULL(s.TestCaseCount, 0) + CASE WHEN ru.InScope > 0 THEN ru.RawTestCaseCount ELSE 0 END AS TestCaseCount,
			ISNULL(s.ChangesetCount, 0) + CASE WHEN ru.InScope > 0 THEN ru.RawChangesetCount ELSE 0 END AS ChangesetCount,
			ISNULL(s.ChildInScope, 0) + CASE WHEN ru.InScope > 0 THEN 1 ELSE 0 END as ChildInScope
		FROM @Rollups ru LEFT OUTER JOIN @Sums s ON s.ID = ru.ID 
		WHERE ru.[Level] = @Level
	)
	INSERT @Sums
		SELECT i.ParentID as ID, 
			SUM(i.Remaining) AS Remaining,
			SUM(i.Completed) AS Completed,
			SUM(i.ActiveBugs) AS ActiveBugs,
			SUM(i.ResolvedBugs) AS ResolvedBugs,
			SUM(i.ClosedBugs) AS ClosedBugs,
			SUM(i.ProposedBugs) AS ProposedBugs,
			SUM(i.ActiveBugs)+ SUM(i.ResolvedBugs) + SUM(i.ClosedBugs) + SUM(i.ProposedBugs) AS TotalBugCount,
			SUM(i.Passed) AS Passed,
			SUM(i.Failed) AS Failed,
			SUM(i.NotRun) AS NotRun,
			SUM(i.TestCaseCount) AS TestCaseCount,
			SUM(i.ChangesetCount) AS ChangesetCount,
			SUM(i.ChildInScope) AS ChildInScope
		FROM IncludeRaw i
		GROUP BY i.ParentID 

	SET @Level = @Level - 1;
END;

--SELECT * FROM @SUMS ORDER BY [ID]
-- Merge the sums into the rollup table
UPDATE @Rollups 
	SET 
		Remaining = ISNULL(sums.Remaining, 0) + CASE WHEN rollups.InScope > 0 THEN rollups.Remaining ELSE 0 END,
		Completed = ISNULL(sums.Completed, 0) + CASE WHEN rollups.InScope > 0 THEN rollups.Completed ELSE 0 END,
		-- Remaining = ISNULL(sums.Remaining, CASE WHEN rollups.InScope > 0 THEN rollups.Remaining ELSE 0 END),   -- use if own totals are not to be included in rollup
		-- Completed = ISNULL(sums.Completed, CASE WHEN rollups.InScope > 0 THEN rollups.Completed ELSE 0 END), -- use if own totals are not to be included in rollup
		Passed = ISNULL(sums.Passed, 0) +CASE WHEN rollups.InScope > 0 THEN  rollups.RawPassed ELSE 0 END,
		Failed = ISNULL(sums.Failed, 0) +CASE WHEN rollups.InScope > 0 THEN  rollups.RawFailed ELSE 0 END,
		NotRun = ISNULL(sums.NotRun, 0) + CASE WHEN rollups.InScope > 0 THEN rollups.RawNotRun ELSE 0 END,
		TestCaseCount = ISNULL(sums.TestCaseCount, 0) + CASE WHEN rollups.InScope > 0 THEN rollups.RawTestCaseCount ELSE 0 END,
		ActiveBugs = ISNULL(sums.ActiveBugs, 0) +CASE WHEN rollups.InScope > 0 THEN  rollups.RawActiveBugs ELSE 0 END,
		ResolvedBugs = ISNULL(sums.ResolvedBugs, 0) + CASE WHEN rollups.InScope > 0 THEN rollups.RawResolvedBugs ELSE 0 END,
		ClosedBugs = ISNULL(sums.ClosedBugs, 0) + CASE WHEN rollups.InScope > 0 THEN rollups.RawClosedBugs ELSE 0 END,
		ProposedBugs = ISNULL(sums.ProposedBugs, 0) + CASE WHEN rollups.InScope > 0 THEN rollups.RawProposedBugs ELSE 0 END,
		TotalBugCount = ISNULL(sums.TotalBugCount, 0) + CASE WHEN rollups.InScope > 0 THEN rollups.TotalBugCount ELSE 0 END, --**** RawTotalBugCount??
		ChangesetCount = ISNULL(sums.ChangesetCount, 0) + CASE WHEN rollups.InScope > 0 THEN rollups.RawChangesetCount ELSE 0 END,
		ChildInScope = ISNULL(sums.ChildInScope, 0) + rollups.InScope
	FROM @Rollups rollups 
	LEFT OUTER JOIN @Sums sums ON rollups.ID = sums.ID;
;
--SELECT * FROM @Rollups ORDER BY [Path]
--
-- Remove Tasks and other contributing leaves
--
IF @IncludeTasks = 0
BEGIN
	DELETE FROM @Rollups WHERE  WIT NOT IN (@DeliverableCategory)
END;

-- Add extra rows for parents that have their own values as well as values rolled up from children.
 -- Keep the parent correct for Max calculations; create new ID so as to avoid looking like a root.  Substitute Raw values into reported fields.
--IF (@DisplayOwnValues &gt; 0)
--BEGIN
--	Declare @OwnValuesTag nvarchar(20);
--	Set @OwnValuesTag = '[' + @OwnValues + ']';

--	INSERT @Rollups
--		SELECT -ID as ID, ID as ParentID, [Level] + 1 as [Level], CAST([Path] + '/-' + STR(ID) AS nvarchar(256)) AS [Path], WorkItemType, [Project], [State],
--			RawRemaining as Remaining, RawCompleted as Completed, RawRemaining,	RawCompleted, StackRank,
--			RawActiveBugs, RawResolvedBugs, RawClosedBugs, RawProposedBugs,
--			RawActiveBugs as ActiveBugs, RawResolvedBugs as ResolvedBugs, RawClosedBugs as ClosedBugs, RawProposedBugs as ProposedBugs, TotalBugCount, BugsIdList,
--			RawPassed, RawFailed, RawNotRun, RawTestCaseCount,
--			RawPassed as Passed, RawFailed as Failed, RawNotRun as NotRun, RawTestCaseCount as TestCaseCount, TestCaseIdList,
--			RawChangesetCount, ChangesetCount, ChangesetIdList,
--			WIT, @OwnValuesTag AS Title,
--			InScope, ChildInScope, 
--			IsOpen,
--			AreaPath, IterationPath
--		FROM @Rollups
--		WHERE Remaining &gt; 0
--			OR Completed &gt; 0
--			OR TestCaseCount &gt; RawTestCaseCount AND RawTestCaseCount &gt; 0 
--END;
--SELECT * FROM @Rollups ORDER BY [Path]
DECLARE @RootMax float;
Set @RootMax =
(	SELECT MAX(Remaining + Completed) as MaxTotal
	FROM @Rollups
	WHERE [Level] = 0
);

-- Final Results
SELECT ru.ID, ParentID, [Level], [Path], Title, Project, WIT, 
	WitShort = CASE WorkItemType
			WHEN @Requirement THEN 'RQ'
			WHEN @UseCase THEN 'UC'
			WHEN @Task THEN 'TK'
			WHEN @TestCase THEN 'TC'
			WHEN @Bug THEN 'BG'
			WHEN @ChangeRequest THEN 'CR'
			WHEN @SupportTicket THEN 'ST'
			WHEN @UserStory THEN 'US'
			ELSE ''
		END,
	[State], Completed, Remaining, StackRank,
	InScope, -- 1 if this requirement/story's Area and Iteration are within the filter parameters and it is not Closed
	IsOpen, -- 0 if this requirement/story's state is Closed
	CASE WHEN Remaining+Completed > 1 THEN 1.0*Remaining/(Remaining+Completed) ELSE 0 END AS RemainingPercent,
	CASE WHEN Remaining+Completed > 1 THEN 1.0*Completed/(Remaining+Completed) ELSE 0 END AS CompletedPercent,
	@RootMax as MaxTotal,
	ActiveBugs, ResolvedBugs, ClosedBugs, ProposedBugs, TotalBugCount, ISNULL(BugsIdList, '') AS BugsIdList,	
	Passed, Failed, NotRun, TestCaseCount, ISNULL(TestCaseIdList, '') AS TestCaseIdList,
	CASE WHEN (Passed+Failed+NotRun) > 0 THEN 1.0*Passed/(Passed+Failed+NotRun) ELSE 0 END AS PassedPercent,
	CASE WHEN (Passed+Failed+NotRun) > 0 THEN 1.0*Failed/(Passed+Failed+NotRun) ELSE 0 END AS FailedPercent,
	CASE WHEN (Passed+Failed+NotRun) > 0 THEN 1.0*NotRun/(Passed+Failed+NotRun) ELSE 0 END AS NotRunPercent,
	ChangesetCount, ISNULL(ChangesetIdList, '') AS ChangesetIdList,
	AreaPath, IterationPath
FROM @Rollups ru
WHERE (InScope > 0 OR ChildInScope > 0) -- show if this or any descendant is within the selected Area(s) and Iteration(s)
--ORDER BY StackRank, Title;
ORDER BY [Path]